package com.hotel.dao.impl;

import com.hotel.dao.UserDao;
import com.hotel.entity.User;
import com.hotel.exception.EntityException;
import com.hotel.utils.JdbcUtils;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @Author Str
 * @Date 2021/10/28 20:08
 * @Description :
 */
public class UserDaoImpl implements UserDao {
    @Override
    public List<User> getUsers() throws SQLException {
        String sqlQuery = "select u.*,r.roleName from user u left join role r on u.roleId=r.id";
        List<User> users = JdbcUtils.queryList(sqlQuery, User.class);
        return users;
    }

    @Override
    public User getUserByAccount(String account) throws SQLException, EntityException {
        //{1}获取一个普通用户 (不含管理员)
        String sql = "select u.*, r.roleName " +
                " from user u left join role r " +
                " on u.roleId = r.id " +
                " where u.account=? ";
        User user = JdbcUtils.queryObject(
                sql, User.class, account);
        //{1}如果我是一定要得到这个 user, 但拿不到, 报出异常。
//        if (user == null) {
//            throw new EntityException("此用户不存在");
//        }
        //{2}这里由于使用了 QueryRunner 无法关闭 rs, pmst.
        return user;
    }

    @Override
    public User getUserById(String id) throws SQLException {
        //{1}编写 sql 查询语句。
        String sql = "select u.*, r.roleName from user u" +
                " left join role r on u.roleId = r.id " +
                " where u.id = ?";
        //{2}执行 sql 语句。
        User user = JdbcUtils.queryObject(sql, User.class, id);
        //{3}为空则, 抛出一个异常。
        if (user == null) {
            throw new SQLException("没有此用户");
        }
        return user;
    }

    /**
     * 获取搜索语句片段
     * map是url参数列表的map集合
     *
     * @return java.lang.String
     */
    @Override
    public String getSearchSQL(Map<String, Object> map) {
        //{ps}这个 map 进来这个方法之前先把 page, limit 拿掉。
        Set<String> keySet = map.keySet();
        StringBuilder sb = new StringBuilder();
        for (String key : keySet) {
            Object word = map.get(key);
            sb.append(" and u." + key
                    + " like '%" + word + "%'");
        }
        return sb.toString();
    }

    /**
     * sqlPart是getSearchSQL方法的返回值
     *
     * @return int
     */
    @Override
    public int getUserCount(String sqlPart) throws SQLException {
        //{1}做 sql 拼接。
        String sql = "select count(*) from user u " +
                " left join role r on u.roleid=r.id " +
                " where r.id!='*' " + sqlPart;
        //{2}查询用户数量。
        Long count = (Long) JdbcUtils.queryValue(sql);
        //{3}做类型转换 ----> int
        return count.intValue();
    }

    /**
     * sqlPart：getSearchSQL方法的返回值
     * offset：偏移量
     * limit：每页读取的数量
     * offset = (页码数 - 1) * 每页的数量;
     *
     * @return java.util.List<com.hotel.entity.User>
     */
    @Override
    public List<User> getUserList(String sqlPart, Integer offset, Integer limit) throws SQLException {
        String sql = "select u.*, r.roleName from user u " +
                " left join role r on u.roleId=r.id " +
                " where r.id!='*' " + sqlPart +
                " order by u.id limit ?, ?";
        List<User> list = JdbcUtils.queryList(sql, User.class, offset, limit);
        return list;
    }

    @Override
    public int addUser(User user) throws SQLException {
        String fields = "id, account, nickName," +
                "password, roleId, no," +
                "sex, createDate";
        String sql = "insert into user(" + fields + ") " +
                " value(?,?,?,?,?,?,?,now())";
        int updateCnt = JdbcUtils.update(sql, user.getId(),
                user.getAccount(), user.getNickName(),
                user.getPassword(), user.getRoleId(),
                user.getNo(), user.getSex());
//        System.out.println("updateCnt:::"+updateCnt);
        return updateCnt;
    }

    @Override
    public int updateUser(User user) throws SQLException {
        // 账号不可改
        String fields = "nickName=?, password=?," +
                " roleId=?, no=?, sex=? ";
        String sql = "update user set " + fields +
                " where id=?";
        int updateCnt = JdbcUtils.update(sql, user.getNickName(),
                user.getPassword(), user.getRoleId(),
                user.getNo(), user.getSex(),
                user.getId());
        return updateCnt;
    }

    @Override
    public int deleteUser(String id) throws SQLException {
        String sqlDelete = "delete from user where id=?";
        int update = JdbcUtils.update(sqlDelete, id);
        //{3}为空则, 抛出一个异常。
//        if (update == 0) {
//            throw new SQLException("没有此用户");
//        }
        return update;
    }

    public static void main(String[] args)
            throws SQLException {
        HashMap<String, Object> map = new HashMap<>();
        map.put("roleId", "1");
        UserDao dao = new UserDaoImpl();
        String sqlPart = dao.getSearchSQL(map);
        System.out.println("SQL 搜索语句片段:");
        System.out.println(sqlPart);

        int count = dao.getUserCount(sqlPart);
        System.out.println("用户数:" + count);

        int page = 2;
        int limit = 10;
        int offset = (page - 1) * 10;
        List<User> list = dao.getUserList(
                sqlPart, offset, limit);
        for (User user : list) {
            System.out.println(user);
        }
    }
}
